﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Windows.Forms;
using Autodesk.Revit.DB;

namespace YJKRevitFamilyDataBase
{
	internal class SqlServerTypeTable : ITypeTable
	{
		public SqlServerTypeTable(FamilyLibDatabase db)
		{
			this.mDatabase = db;
		}

		public bool Add(DataTable typeTable, DbFamily family, DbTransaction tr)
		{
			if (typeTable.Columns.Contains("TypeId"))
			{
				typeTable.Columns.Remove("TypeId");
			}
			string str = string.Concat(new string[]
			{
				"insert into [",
				family.Guid.ToString(),
				"]([",
				typeTable.Columns[0].ColumnName,
				"]"
			});
			for (int i = 1; i < typeTable.Columns.Count; i++)
			{
				str = str + ",[" + typeTable.Columns[i].ColumnName + "]";
			}
			str += ") values (";
			for (int j = 0; j < typeTable.Rows.Count; j++)
			{
				if (typeTable.Rows[j]["规格名"].ToString().Trim() == "")
				{
					MessageBox.Show(family.Name + "族，第" + (j + 1).ToString() + "条记录规格名为空,添加失败。", "族库管理");
				}
				else
				{
					string text = str + "'" + typeTable.Rows[j][0].ToString() + "'";
					for (int k = 1; k < typeTable.Columns.Count; k++)
					{
						text = text + ",'" + typeTable.Rows[j][k].ToString() + "'";
					}
					text += ")";
					try
					{
						this.mDatabase.ExecuteCommand(text, tr);
					}
					catch
					{
						MessageBox.Show(family.Name + "族，第" + (j + 1).ToString() + "条记录添加失败，请检查规格名是否重名。", "族库管理");
					}
				}
			}
			typeTable.Rows.Clear();
			return true;
		}

		public void Update(DataTable typeTable, DbFamily family)
		{
			for (int i = 0; i < typeTable.Rows.Count; i++)
			{
				if (typeTable.Rows[i]["规格名"].ToString().Trim() == "")
				{
					MessageBox.Show("第" + (i + 1).ToString() + "条记录规格名为空,更新失败。", "族库管理");
				}
				else
				{
					string text = string.Format("UPDATE [{0}] SET [{1}]='{2}'", family.Guid.ToString(), typeTable.Columns[1].ColumnName, typeTable.Rows[i][1]);
					for (int j = 2; j < typeTable.Columns.Count; j++)
					{
						text += string.Format(",[{0}]='{1}'", typeTable.Columns[j].ColumnName, typeTable.Rows[i][j]);
					}
					text += string.Format(" WHERE [TypeId]='{0}'", typeTable.Rows[i]["TypeId"]);
					if (!this.mDatabase.ExecuteCommand(text))
					{
						MessageBox.Show("第" + i.ToString() + "条记录修改失败");
					}
				}
			}
		}

		public DbFamilySymbol this[DbFamily family, string name]
		{
			get
			{
				DbFamilySymbol dbFamilySymbol = new DbFamilySymbol(family, name);
				dbFamilySymbol.SetDatabase(this.mDatabase);
				string sqlStr = string.Format("select * from [{0}] where [规格名]='{1}'", family.Guid.ToString(), name.Trim());
				DataTable dataTable = this.mDatabase.ExecQuery(sqlStr);
				if (dataTable != null && dataTable.Rows.Count > 0)
				{
					dataTable.Columns.Remove("规格名");
					dataTable.Columns.Remove("TypeId");
					IFamilyParamTable familyParamTable = new SqliteFamilyParamTable(this.mDatabase);
					for (int i = 0; i < dataTable.Columns.Count; i++)
					{
						string columnName = dataTable.Columns[i].ColumnName;
						DbParamDefinition dbParamDefinition = familyParamTable[family, columnName];
						if (dbParamDefinition != null && dbParamDefinition.ParamDefinitionType != ParamDefinitionType.InstanceParam)
						{
							DbParameter item = new DbParameter(dbParamDefinition, dataTable.Rows[0][i]);
							dbFamilySymbol.Params.Add(item);
						}
					}
				}
				return dbFamilySymbol;
			}
		}

		DataTable ITypeTable.SelectTypes(string SQL)
		{
			throw new NotImplementedException();
		}

		public void AddColumns(DbFamily family, List<DbParamDefinition> paramDefs, DbTransaction tr)
		{
			for (int i = 0; i < paramDefs.Count; i++)
			{
				try
				{
					this.mDatabase.ExecuteCommand(string.Concat(new string[]
					{
						"alter table [",
						family.Guid.ToString(),
						"] add [",
						paramDefs[i].Name,
						"] varchar(50)"
					}), tr);
				}
				catch
				{
				}
			}
		}

		public bool Create(DbFamily family, DbTransaction tr)
		{
			bool result;
			try
			{
				string text = string.Format("create table [{0}] ([TypeId] integer PRIMARY KEY identity,[规格名] varchar(100) NOT NULL unique", family.Guid.ToString());
				for (int i = 0; i < family.ParamDefinitionList.Count; i++)
				{
					string typeStringFromStorageType = this.getTypeStringFromStorageType(family.ParamDefinitionList[i].StorageType);
					if (family.ParamDefinitionList[i].ParamDefinitionType == ParamDefinitionType.CustomParam || family.ParamDefinitionList[i].ParamDefinitionType == ParamDefinitionType.TypeParam)
					{
						text += string.Format(",[{0}] {1}", family.ParamDefinitionList[i].Name.Trim(), typeStringFromStorageType);
					}
				}
				text += ");";
				if (this.mDatabase.ExecuteCommand(text, tr))
				{
					result = true;
				}
				else
				{
					result = false;
				}
			}
			catch (Exception ex)
			{
				throw ex;
			}
			return result;
		}

		private string getTypeStringFromStorageType(StorageType storageType)
		{
			string result = "varchar(100)";
            if (storageType == null || (int)storageType == 3 || (int)storageType == 4)
			{
				result = "varchar(100)";
			}
            else if ((int)storageType == 1)
			{
				result = "integer";
			}
            else if ((int)storageType == 2)
			{
				result = "real";
			}
			return result;
		}

		public bool Drop(DbFamily family, DbTransaction tr)
		{
			bool result;
			try
			{
				if (this.mDatabase.ExecuteCommand("drop table [" + family.Guid.ToString() + "]", tr))
				{
					result = true;
				}
				else
				{
					result = false;
				}
			}
			catch (Exception ex)
			{
				throw ex;
			}
			return result;
		}

		public DataTable DataTable(DbFamily family)
		{
			List<DbParamDefinition> list = new SqliteFamilyParamTable(this.mDatabase)[family];
			list.Sort(new ParamDefinitionComp());
			string text = "[TypeId],[规格名]";
			for (int i = 0; i < list.Count; i++)
			{
				if (list[i].ParamDefinitionType != ParamDefinitionType.InstanceParam)
				{
					text = text + ",[" + list[i].Name + "]";
				}
			}
			return this.mDatabase.ExecQuery(string.Concat(new string[]
			{
				"select ",
				text,
				" from [",
				family.Guid.ToString(),
				"]"
			}));
		}

		public List<string> this[DbFamily family]
		{
			get
			{
				List<string> list = new List<string>();
				DataTable dataTable = this.DataTable(family);
				for (int i = 0; i < dataTable.Rows.Count; i++)
				{
					list.Add(dataTable.Rows[i]["规格名"].ToString());
				}
				return list;
			}
		}

		public bool Remove(int typeId, DbFamily family)
		{
			string commandStr = string.Concat(new object[]
			{
				"delete from [",
				family.Guid.ToString(),
				"] where [TypeId]='",
				typeId,
				"'"
			});
			return this.mDatabase.ExecuteCommand(commandStr);
		}

		public bool Create(DbFamily family, DataTable dt, DbTransaction tr)
		{
			bool result;
			try
			{
				string text = string.Format("create table [{0}] ([{1}] integer PRIMARY KEY identity", family.Guid.ToString(), dt.Columns[0].ColumnName.Trim());
				for (int i = 1; i < dt.Columns.Count; i++)
				{
					string typeStringFromType = this.getTypeStringFromType(dt.Columns[i].DataType);
					text += string.Format(",[{0}] {1}", dt.Columns[i].ColumnName.Trim(), typeStringFromType);
				}
				text += ");";
				if (this.mDatabase.ExecuteCommand(text, tr))
				{
					result = true;
				}
				else
				{
					result = false;
				}
			}
			catch (Exception ex)
			{
				throw ex;
			}
			return result;
		}

		private string getTypeStringFromType(Type type)
		{
			string result = "varchar(50)";
			if (type == typeof(string))
			{
				result = "varchar(50)";
			}
			else if (type == typeof(int) || type == typeof(long))
			{
				result = "int";
			}
			else if (type == typeof(double))
			{
				result = "real";
			}
			else if (type == null)
			{
				result = "DBNull";
			}
			return result;
		}

		public FamilySymbol CreateFamilySymbol(DbFamily family, string name, Autodesk.Revit.DB.Document doc)
		{
			throw new NotImplementedException();
		}

		public FamilySymbol CreateFamilySymbol(DbFamily family, string name, Autodesk.Revit.DB.Document doc, ref int host)
		{
			throw new NotImplementedException();
		}

		private FamilyLibDatabase mDatabase;
	}
}
